# -*- coding: utf-8 -*-
# @Time: 2025/2/6 18:46
# @Author: foxhuty
# @File: df_notes.py
import pandas as pd
import numpy as np



# def get_subject_max_min_score(data, subject):
#     """
#     计算各等级的卷面分区间（Y1-Y2），处理同分学生导致的等级人数溢出。
#     :param data: DataFrame，包含学生成绩数据
#     :param subject: str，科目名称
#     :return: 各等级的最大分列表和最小分列表
#     """
#     # 复制数据以避免修改原始数据
#     data_sorted = data.sort_values(by=subject, ascending=False, ignore_index=True)
#     subject_num = data_sorted[subject].count()
#     max_score = []
#     min_score = []
#
#     # 计算各等级的理论人数
#     A_num = int(subject_num * 0.15)
#     B_num = int(subject_num * 0.35)
#     C_num = int(subject_num * 0.35)
#     D_num = int(subject_num * 0.13)
#     E_num = subject_num - (A_num + B_num + C_num + D_num)
#
#     # 动态跟踪当前处理位置
#     start = 0
#
#     # 处理A等级
#     if A_num > 0:
#         end = start + A_num - 1
#         A_slice = data_sorted.loc[start:end, subject]
#         A_min = A_slice.min()
#         A_max = A_slice.max()
#         # 获取所有同分学生
#         A_final = data_sorted[data_sorted[subject] >= A_min]
#         A_count = len(A_final)
#         start += A_count
#     else:
#         A_count = 0
#         A_min = A_max = None
#     max_score.append(A_max)
#     min_score.append(A_min)
#
#     # 处理B等级
#     B_actual_num = min(B_num, subject_num - start)
#     if B_actual_num > 0:
#         end = start + B_actual_num - 1
#         B_slice = data_sorted.loc[start:end, subject]
#         B_min = B_slice.min()
#         B_max = B_slice.max()
#         B_final = data_sorted.iloc[start:][data_sorted[subject] >= B_min]
#         B_count = len(B_final)
#         start += B_count
#     else:
#         B_count = B_min = B_max = 0
#     max_score.append(B_max if B_actual_num > 0 else None)
#     min_score.append(B_min if B_actual_num > 0 else None)
#
#     # 处理C等级
#     C_actual_num = min(C_num, subject_num - start)
#     if C_actual_num > 0:
#         end = start + C_actual_num - 1
#         C_slice = data_sorted.loc[start:end, subject]
#         C_min = C_slice.min()
#         C_max = C_slice.max()
#         C_final = data_sorted.iloc[start:][data_sorted[subject] >= C_min]
#         C_count = len(C_final)
#         start += C_count
#     else:
#         C_count = C_min = C_max = 0
#     max_score.append(C_max if C_actual_num > 0 else None)
#     min_score.append(C_min if C_actual_num > 0 else None)
#
#     # 处理D等级
#     D_actual_num = min(D_num, subject_num - start)
#     if D_actual_num > 0:
#         end = start + D_actual_num - 1
#         D_slice = data_sorted.loc[start:end, subject]
#         D_min = D_slice.min()
#         D_max = D_slice.max()
#         D_final = data_sorted.iloc[start:][data_sorted[subject] >= D_min]
#         D_count = len(D_final)
#         start += D_count
#     else:
#         D_count = D_min = D_max = 0
#     max_score.append(D_max if D_actual_num > 0 else None)
#     min_score.append(D_min if D_actual_num > 0 else None)
#
#     # 处理E等级
#     E_final = data_sorted.iloc[start:]
#     E_count = len(E_final)
#     E_min = E_final[subject].min() if E_count > 0 else None
#     E_max = E_final[subject].max() if E_count > 0 else None
#     max_score.append(E_max)
#     min_score.append(E_min)
#
#     # 打印结果
#     print(f"{subject}学科等级人数：A-{A_count}, B-{B_count}, C-{C_count}, D-{D_count}, E-{E_count}")
#     print(f"Y1: {min_score}, Y2: {max_score}")
#     return max_score, min_score

def get_subject_max_min_score(data, subject):
    """
    计算各等级的卷面分区间（Y1-Y2），正确处理同分导致的等级人数溢出
    :param data: 包含学生成绩的DataFrame
    :param subject: 要处理的科目名称
    :return: (各等级最大分列表, 各等级最小分列表)
    """
    data_sorted = data.sort_values(by=subject, ascending=False, ignore_index=True)
    subject_num = len(data_sorted)
    max_score = []
    min_score = []
    start = 0  # 当前处理位置

    # 各等级理论人数
    level_ratios = [0.15, 0.35, 0.35, 0.13]
    level_names = ['A', 'B', 'C', 'D']
    level_counts = [int(subject_num * ratio) for ratio in level_ratios]
    level_counts.append(subject_num - sum(level_counts))  # E等级人数

    # 处理前四个等级（A-D）
    for i in range(4):
        lvl_name = level_names[i]
        target_num = level_counts[i]

        if target_num <= 0 or start >= subject_num:
            max_score.append(None)
            min_score.append(None)
            continue

        # 获取理论区间
        end = min(start + target_num - 1, subject_num - 1)
        lvl_slice = data_sorted.loc[start:end, subject]
        lvl_min = lvl_slice.min()
        lvl_max = lvl_slice.max()

        # 获取所有同分学生
        remaining_data = data_sorted.iloc[start:]  # 关键修改点
        actual_lvl = remaining_data[remaining_data[subject] >= lvl_min]
        actual_count = len(actual_lvl)

        # 更新区间
        start += actual_count
        max_score.append(lvl_max)
        min_score.append(lvl_min if actual_count > 0 else None)

    # 处理E等级
    E_count = subject_num - start
    E_slice = data_sorted.iloc[start:]
    E_min = E_slice[subject].min() if E_count > 0 else None
    E_max = E_slice[subject].max() if E_count > 0 else None
    max_score.append(E_max)
    min_score.append(E_min)

    # 打印验证
    print(f"[{subject}] 各等级人数: "
          f"A={level_counts[0]}→{max_score[0]}-{min_score[0]}, "
          f"B={level_counts[1]}→{max_score[1]}-{min_score[1]}, "
          f"E剩余={E_count}人")
    return max_score, min_score


if __name__ == '__main__':
    file_path = r'D:\data_test_files\成都十一中高2023级半期考试.xlsx'
    data_df = pd.read_excel(file_path, sheet_name='总表')
    print(data_df.head())
    added_columns = [col for col in data_df.columns.tolist() if col in ['政治', '化学', '地理', '生物']]
    print(added_columns)
    for column in added_columns:
        get_subject_max_min_score(data_df,column)
